package com.yhp.dao.iml;

import com.yhp.bean.Student;
import com.yhp.dao.StudentDao;
import com.yhp.util.DbUtils;
import com.yhp.util.StudentEnum;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DbUtils implements StudentDao {
    @Override
    public int total(String stuname, String stuno, int sex) {
        int total = 0;
        List params = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer(" select count(*) from student where 1=1 and state!=4 ");
            if (stuname != null && stuname.length() > 0) {
                sql.append(" and stuname like ? ");
                params.add("%" + stuname + "%");
            }
            if (stuno != null && stuno.length() > 0) {
                sql.append(" and stuno=? ");
                params.add(stuno);
            }
            if (sex != -1) {
                sql.append(" and sex=? ");
                params.add(sex);
            }
            resultSet = query(sql.toString(), params);
            while (resultSet.next()) {
                total = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }


    @Override
    public List<Student> getStudent(String stuname, String stuno, int sex, int pageIndex, int pageSize) {
        List list = new ArrayList();
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer(" select * from student where 1=1 and state!=4 ");
        if (stuname != null && stuname.length() > 0) {
            sql.append(" and stuname like ? ");
            params.add("%" + stuname + "%");
        }
        if (stuno != null && stuno.length() > 0) {
            sql.append(" and stuno=? ");
            params.add(stuno);
        }
        if (sex != -1) {
            sql.append(" and sex=? ");
            params.add(sex);
        }
        //分页
        sql.append(" limit ?,? ");//1  5  limit 0 5
        //limit (pageIndex-1)*pageSize,pageSize
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);


        try {
            resultSet = query(sql.toString(), params);

            while (resultSet.next()) {
                Student student = new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                student.setStuno(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setAddress(resultSet.getString("address"));
                student.setProfession(resultSet.getString("profession"));
                student.setIdnumber(resultSet.getString("idnumber"));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }

    @Override
    public int insert(Student student) {
        int update = 0;
        try {
            String sql = "insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List params = new ArrayList();
            params.add(student.getStuname());
            params.add(student.getStuno());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdnumber());
            params.add(student.getPolitics());
            params.add(new Date());
            params.add(StudentEnum.READING.type);
            params.add(student.getIntroduction());
            params.add(student.getGid());
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    /**
     * 主键查询学生信息
     *
     * @param stuId
     * @return student
     */
    @Override
    public Student findById(int stuId) {
        Student student = new Student();

        try {
            String sql = "select * from student where stuid=?";
            List params = new ArrayList();
            params.add(stuId);
            resultSet = query(sql, params);
            while (resultSet.next()) {
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuno(resultSet.getString("stuno"));
                student.setStuname(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setRegdate(resultSet.getDate("regdate"));
                student.setIdnumber(resultSet.getString("idnumber"));
                student.setProfession(resultSet.getString("profession"));
                student.setAddress(resultSet.getString("address"));
                student.setRegistered(resultSet.getString("registered"));
                student.setEmail(resultSet.getString("email"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setPolitics(resultSet.getString("politics"));
                student.setGid(resultSet.getInt("gid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int updateStudent(Student student) {
        int update = 0;
        try {
            String sql = "update student set stuname=?,stuno=?,sex=?,phone=?,email=?,registered=?,address=?,profession=?," +
                    "idnumber=?,politics=?,introduction=?,gid=? where stuid=?";
            List params = new ArrayList();
            params.add(student.getStuname());
            params.add(student.getStuno());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdnumber());
            params.add(student.getPolitics());
            params.add(student.getIntroduction());
            params.add(student.getGid());
            params.add(student.getStuid());
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    /**
     * 删除学生信息
     *
     * @param stuid
     * @return
     */
    @Override
    public int delStudent(String stuid) {
        int update = 0;
        try {
            String sql = "update student set state=? where stuid=?";
            List list = new ArrayList();
            list.add(StudentEnum.DELETE.type);
            list.add(stuid);
            update = update(sql, list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public Student checkByStuNo(String StuNo) {
        Student student = null;
        try {
            String sql = "select * from student where stuno=?";
            List list = new ArrayList();
            list.add(StuNo);
            resultSet = query(sql, list);
            while (resultSet.next()) {
                student = new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                student.setStuno(resultSet.getString("stuno"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }


}
